﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DAL
{
    public partial class DatabaseObject
    {
        string constr = ConfigurationManager.ConnectionStrings["myConnectionStrings"].ConnectionString;
        public SqlConnection cnn;
        public SqlDataAdapter da;
        public DataTable dt;
        public SqlCommand cmd;

        public void Connect()
        {
            cnn = new SqlConnection(constr);
            cnn.Open();
        }
        public void Disconnect()
        {
            if (cnn.State == ConnectionState.Open)
            {
                cnn.Close();
            }
        }

        protected DataTable ReturnDataTable_WithParameter(string storeprocedure, SqlParameter[] sql)
        {
            Connect();
            dt = new DataTable();
            try
            {
                cmd = new SqlCommand();
                cmd.CommandTimeout = 2000;
                cmd.Connection = cnn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = storeprocedure;
                for (int i = 0; i < sql.Length; i++)
                {
                    cmd.Parameters.Add(sql[i]);
                }
                da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(dt);
                Disconnect();

            }
            catch (SqlException ex)
            {
                string Error = ex.Message;
            }
            finally
            {
                if (cnn.State == ConnectionState.Open)
                {
                    Disconnect();
                }

            }
            return dt;
        }
    }
}
